package org.grc.app.z_project.z_framework.helper;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.grc.app.z_project.z_framework.ConfigHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库操作工具类
 * 
 * @author grc
 * @since 1.0-SNAPSHOT
 */
public class DataBaseHelper {

	private static final Logger LOGGER = LoggerFactory.getLogger(DataBaseHelper.class);
	private static final QueryRunner QUERY_RUNNER = new QueryRunner();
	private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<Connection>();
	private static final BasicDataSource DATA_SOURCE;
	
	private static final String DRIVER;
	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;
	
	
	static {
		DRIVER = ConfigHelper.getJdbcDriver();
		URL = ConfigHelper.getJdbcUrl();
		USERNAME = ConfigHelper.getJdbcUserName();
		PASSWORD = ConfigHelper.getJdbcUserPassword();
		
		DATA_SOURCE = new BasicDataSource();
		DATA_SOURCE.setDriverClassName(DRIVER);
		DATA_SOURCE.setUrl(URL);
		DATA_SOURCE.setUsername(USERNAME);
		DATA_SOURCE.setPassword(PASSWORD);
		
		try {
			Class.forName(DRIVER);
		}
		catch (ClassNotFoundException e) {
			LOGGER.error("can not load jdbc driver", e);
		}
	}
	/*
	 * 获取数据库连接
	 */
	public static Connection getConnection () {
		Connection conn = CONNECTION_HOLDER.get();
		if (conn == null) {
			try {
				conn = DATA_SOURCE.getConnection();
			} catch (SQLException e) {
				LOGGER.error("get connection failure", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(conn);
			}
		}
		return conn;
	}
//	/*
//	 * 关闭数据库连接
//	 */
//	public static void cloesConnection (Connection conn) {
//		if (conn != null) {
//			try {
//				conn.close();
//			}
//			catch (SQLException e) {
//				LOGGER.error("close connection failure", e);
//			}
//		}
//	}
//	/*
//	 * 关闭数据库连接
//	 */
//	public static void cloesConnection () {
//		Connection conn = CONNECTION_HOLDER.get();
//		if (conn != null) {
//			try {
//				conn.close();
//			} catch (SQLException e) {
//				LOGGER.error("close connection failure", e);
//			} finally {
//				CONNECTION_HOLDER.remove();
//			}
//		}
//	}
	/**
	 * 查询实体列表
	 */
	public static <T> List<T> queryEntityList (Class<T> entityClazz, String sql, Object ... params) {
		List<T> entityList = null;
		Connection conn = getConnection();
		try {
			entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClazz), params);
		} catch (SQLException e) {
			LOGGER.error("query entity list failuer", e);
			throw new RuntimeException(e);
		}
		return entityList;
	}
	/**
	 * 执行数据库语句更新（包括update, insert, delete）
	 */
	public static int excuteUpdate (String sql, Object ... params) {
		int rows = 0;
		try {
			Connection conn = getConnection();
			rows = QUERY_RUNNER.update(conn, sql, params);
		} catch (SQLException e) {
			LOGGER.error("execute update failure", e);
			throw new RuntimeException(e);
		}
		return rows;
	}
	/**
	 * 执行sql文件
	 */
	public static void excuteSQLFile (String filePath) {
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		try {
			String sql = null;
			while ((sql = br.readLine()) != null){
				excuteUpdate(sql);
			}
		} catch (IOException e) {
			LOGGER.error("excute sql file failure", e);
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 开启事务
	 */
	public static void beginTransaction () {
		Connection conn = getConnection();
		if (conn != null) {
			try {
				conn.setAutoCommit(Boolean.FALSE);
			} catch (SQLException e) {
				LOGGER.error("begin transaction failure!", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(conn);
			}
		}
	}
	
	/**
	 * 提交事务
	 */
	public static void commitTransaction () {
		Connection conn = getConnection();
		if (conn != null) {
			try {
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				LOGGER.error("commit transaction failure!", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}
	
	/**
	 * 回滚事务
	 */
	public static void rollbackTransaction () {
		Connection conn = getConnection();
		if (conn != null) {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
				LOGGER.error("rollback transaction failure", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}
}
